#！/bin/bash
source /etc/profile
# 手动输入开始日期和结束日期
start_date=$1
end_date=$2
# 定义要执行的SQL
# 汇总得到每日活跃会员信息；每日数据汇总得到每周、每月数据
sql="insert overwrite table ads.ads_continue_login_3_days
partition(dt='$end_date')
SELECT
	device_id,
	uid,
	app_v,
	os_type,
	LANGUAGE,
	channel,
	area,
	brand,
	gid,
	count(1)
FROM
	(
		SELECT
			device_id,
			uid,
			app_v,
			os_type,
			LANGUAGE,
			channel,
			area,
			brand,
			date_sub(
				dt,
				row_number () over (
					PARTITION BY device_id
					ORDER BY
						dt
				)
			) gid
		FROM
			dws.dws_member_start_day
		WHERE
			dt >= '$start_date'
		AND dt <= '$end_date'
	) tmp
GROUP BY
	device_id,
	uid,
	app_v,
	os_type,
	LANGUAGE,
	channel,
	area,
	brand,
	gid
HAVING
	count(1) >= 3"
hive -e "$sql"
